import os.path
import openpyxl
import datetime
import time
import pymysql
import logging
import sys

timeCur = datetime.datetime.now()
fileNamePath = 'E:\\日常工作\\工单汇总\\考核数据-区级\\'
logFile = fileNamePath + 'AutoImportExcel.log'
# 创建logger对象
logger = logging.getLogger('test_logger')

# 设置日志等级
logger.setLevel(logging.DEBUG)

# 追加写入文件a ，设置utf-8编码防止中文写入乱码
test_log = logging.FileHandler('test.log', 'a', encoding='utf-8')

# 向文件输出的日志级别
test_log.setLevel(logging.DEBUG)

# 向文件输出的日志信息格式
formatter = logging.Formatter('%(asctime)s - %(filename)s - line:%(lineno)d - %(levelname)s - %(message)s -%(process)s')

test_log.setFormatter(formatter)

# 加载文件到logger对象中
logger.addHandler(test_log)

log = logging.getLogger()
stdout_handler = logging.StreamHandler(sys.stdout)
log.addHandler(stdout_handler)


def get_excel_file_name(file_parth_excel, num_month):
    file_name_cur = file_parth_excel + '{}月份考核数据.xlsx'.format(num_month)
    return file_name_cur


def get_work_sheet(file_name):
    book_source = openpyxl.load_workbook(file_name)
    sheets_list = book_source.sheetnames
    # logging.debug('{}:{}-{}'.format(file_name, sheets_list, sheets_list[0]))
    sheet_source = book_source[sheets_list[0]]
    # logging.debug('{}----max_row:{}'.format(file_name, sheet_source.max_row))
    # logging.debug('{}----max_column:{}'.format(file_name, sheet_source.max_column))
    return sheet_source


def get_col_index(sheet_src, col_title):
    num_col_max_src = sheet_src.max_column
    for col in range(1, num_col_max_src+1):
        cell_value_src = sheet_src.cell(1, col).value
        logging.debug('{}/{} : {}'.format(col, num_col_max_src, cell_value_src))
        if cell_value_src is not None:
            cell_value_src = cell_value_src.strip()
            cell_value_src = cell_value_src.replace(" ", "")
            if cell_value_src == col_title:
                return col


col_title_list = ['工单编号', '工单类型', '问题分类', '一级分类', '二级分类', '三级分类', '标签', '标题', '主要内容', '村/社区',
                  '工单状态', '工单来源', '来电人', '来电人电话/账号', '被反映区', '被反映街乡镇', '承办单位', '办理结果', '回复内容',
                  '处理受理方式', '市电话处理方式', '来电时间', '派单时间', '处理时限', '办结时间', '坐席ID', '坐席姓名', '企业名称',
                  '工单请求状态', '派单类型', '截止日期', '时限单位', '派单员', '回访时间', '是否解决', '是否满意', '是否联系',
                  '是否见面', '回访状态', '回访结果', '七有五性', '不纳入类型', '所属辖区', '承办部门', '承办部门（原）', '接单部门',
                  '参与人', '参与部门', '工单性质', '来电主体', '市回复时间', '考核类型', '问题分类反馈', '小区点位', '疏整促',
                  '是否属实', '二次回访', '第一次回访记录描述', '第一次回访员姓名', '回访次数', '黑名单', '是否扣分项', '创建时间',
                  '物业公司']

col_title_list_key = {'工单编号': 'gongdan_id', '工单类型': 'gongdanleixing', '问题分类': 'wentifenlei',
                      '一级分类': 'yijifenlei', '二级分类': 'erjifenlei', '三级分类': 'sanjifenlei', '标签': 'biaoqian',
                      '标题': 'biaoti', '主要内容': 'zhuyaoneirong', '村/社区': 'shequcun', '工单状态': 'gongdanzhuangtai',
                      '工单来源': 'gongdanlaiyuan', '来电人': 'laidianren', '来电人电话/账号': 'laidianhaoma',
                      '被反映区': 'beifanyingqu', '被反映街乡镇': 'beifanyingjiexiangzhen', '承办单位': 'chengbandanwei',
                      '办理结果': 'banlijieguo', '回复内容': 'huifuneirong', '处理受理方式': 'chulishoulifangshi',
                      '市电话处理方式': 'shidianhuachulifangshi', '来电时间': 'laidianshijian', '派单时间': 'paidanshijian',
                      '处理时限': 'chulishixian', '办结时间': 'banjieshijian', '坐席ID': 'zuoxi_id',
                      '坐席姓名': 'zuoxi_xingming', '企业名称': 'qiyemingcheng', '工单请求状态': 'gongdanqingqiuzhuangtai',
                      '派单类型': 'paidanleixing', '截止日期': 'jiezhiriqi', '时限单位': 'shixiandanwei',
                      '派单员': 'paidanyuan', '回访时间': 'huifangshijian', '是否解决': 'shifoujiejue',
                      '是否满意': 'shifoumanyi', '是否联系': 'shifoulianxi', '是否见面': 'shifoujianmian',
                      '回访状态': 'huifangzhuangtai', '回访结果': 'huifangjieguo', '七有五性': 'qiyouwuxing',
                      '不纳入类型': 'bunaruleixing', '所属辖区': 'suoshuxiaqu ', '承办部门': 'chengbanbumen',
                      '承办部门（原）': 'chengbanbumen_yuan', '接单部门': 'jiedanbumen', '参与人': 'canyuren',
                      '参与部门': 'canyubumen', '工单性质': 'gongdanxingzhi', '来电主体': 'laidianzhuti',
                      '市回复时间': 'shihuifushijian', '考核类型': 'kaoheleixing', '问题分类反馈': 'wentifenleifankui',
                      '小区点位': 'xiaoqudianwei', '疏整促': 'cuzhengshu', '是否属实': 'shifoushushi',
                      '二次回访': 'ercihuifang', '第一次回访记录描述': 'diyicihuifangjilumiaoshu',
                      '第一次回访员姓名': 'diyicihuifangyuanxingming', '回访次数': 'huifangcishu', '黑名单': 'heimingdan',
                      '是否扣分项': 'shifoukoufenxiang', '创建时间': 'chuangjianshijian', '物业公司': 'wuyegongsi'}

conn_obj_db = pymysql.connect(
    host='127.0.0.1',  # MySQL服务端的IP地址
    port=3306,  # MySQL默认PORT地址(端口号)
    user='root',  # 用户名
    password='password',  # 密码,也可以简写为passwd
    database='bidatabase',  # 库名称,也可以简写为db
    charset='utf8'  # 字符编码
)
cursor = conn_obj_db.cursor()


def do_execute_sql_select(db_cursor, select_gong_dan_id):
    # noinspection PyBroadException
    sql_select = "select paidanshijian from table_gongdan_kaohe_qu where gongdan_id='{}'".format(select_gong_dan_id)
    # logging.debug("sql_select:{}".format(sql_select))
    try:
        db_cursor.execute(sql_select)
        results = cursor.fetchone()
        return results[0]
    except Exception as e:
        logger.debug("没有查找到数据，sql_select:{}".format(sql_select))
        return ''


def do_update_shi_fou_shan_chu(db_cursor, update_gong_dan_id):
    # noinspection PyBroadException
    update_select = "UPDATE table_gongdan_kaohe_qu gd SET gd.shifoushanchu=4  WHERE gd.gongdan_id='{}'".format(update_gong_dan_id)
    # logging.debug("sql_select:{}".format(sql_select))
    try:
        db_cursor.execute(update_select)
        logger.debug("更新数据成功，update_select:{}".format(update_select))
        return ''
    except Exception as e:
        logger.debug("更新数据失败，[{}] update_select:{}".format(e, update_select))
        return ''


def make_insert_sql(kaohe_year, kaohe_month, sheet_insert, row_num, col_title_list_insert):
    sql_insert = ''
    sql_insert_key = ''
    sql_insert_value = ''

    sql_delete_cur_month = ''

    for col_insert in range(0, len(col_title_list)):
        col_insert_in_excel = col_title_list_insert[col_title_list[col_insert]]
        if col_insert_in_excel is not None:
            cell_value = sheet_insert.cell(row_num, col_insert_in_excel).value
            if cell_value is not None:
                if isinstance(cell_value, str):
                    cell_value = cell_value.replace('\\', '\\\\')
                    cell_value = cell_value.replace('\'', '\\\'')
                if col_title_list[col_insert] == '承办部门':
                    if col_insert != 0:
                        sql_insert_key = sql_insert_key + ','
                        sql_insert_value = sql_insert_value + ','
                    sql_insert_key = sql_insert_key + 'chengbanbumen,chengbanbumen_yuan'
                    sql_insert_value = sql_insert_value + '\'{}\',\'{}\''.format(cell_value, cell_value)
                else:
                    if col_insert != 0:
                        sql_insert_key = sql_insert_key + ','
                        sql_insert_value = sql_insert_value + ','
                    sql_insert_key = sql_insert_key + col_title_list_key[col_title_list[col_insert]]
                    sql_insert_value = sql_insert_value + '\'{}\''.format(cell_value)
                # print('{}:{}'.format(col_title_list_key[col_title_list[col_insert]], cell_value))
    pai_dan_shi_jian_excel_row_str = sheet_insert.cell(row_num, col_title_list_insert['回访时间']).value
    pai_dan_shi_jian_excel_row = datetime.datetime.strptime(pai_dan_shi_jian_excel_row_str, '%Y-%m-%d %H:%M:%S')
    sql_insert_key = sql_insert_key + ',huifangshijian_year'
    sql_insert_value = sql_insert_value + ',\'{}\''.format(kaohe_year)

    sql_insert_key = sql_insert_key + ',huifangshijian_month'
    sql_insert_value = sql_insert_value + ',\'{:02d}\''.format(kaohe_month)

    pai_dan_shi_jian_year_month = '{}-{:02d}'.format(kaohe_year, kaohe_month)
    sql_insert_key = sql_insert_key + ',huifangshijian_year_month'
    sql_insert_value = sql_insert_value + ',\'{}\''.format(pai_dan_shi_jian_year_month)

    cell_gong_dan_id_excel = sheet_insert.cell(row_num, col_title_list_insert['工单编号']).value
    pai_dan_shi_jian_sel = do_execute_sql_select(cursor, cell_gong_dan_id_excel)
    if pai_dan_shi_jian_sel != '':
        time_pai_dan_excel_time_stamp = int(time.mktime(pai_dan_shi_jian_excel_row.timetuple()))
        time_pai_dan_sql_time_stamp = int(time.mktime(pai_dan_shi_jian_sel.timetuple()))
        if time_pai_dan_excel_time_stamp >= time_pai_dan_sql_time_stamp:
            do_update_shi_fou_shan_chu(cursor, cell_gong_dan_id_excel)
        else:
            sql_insert_key = sql_insert_key + ',shifoushanchu'
            sql_insert_value = sql_insert_value + ',4'
    sql_insert = 'INSERT INTO table_gongdan_kaohe_qu(' + sql_insert_key + ')VALUES (' + sql_insert_value + ')'
    return sql_insert


def make_update_sql(sheet_update, row_num, col_title_list_update, gong_dan_id):
    sql_update = ''
    for col_update in range(1, len(col_title_list)):
        col_update_in_excel = col_title_list_update[col_title_list[col_update]]
        if col_update_in_excel is not None:
            cell_value_update = sheet_update.cell(row_num, col_update_in_excel).value
            if cell_value_update is not None:
                if col_update > 1:
                    sql_update = sql_update + ','
                sql_update_key = col_title_list_key[col_title_list[col_update]]
                sql_update = sql_update + "{}='{}'".format(sql_update_key, cell_value_update)
    sql_update = "UPDATE table_gongdan_kaohe_qu SET " + sql_update + " where gongdan_id='{}'".format(gong_dan_id)
    return sql_update


# 打开所有文件
def do_import_excel_data(file_year, num_pai_dan, num_gong_dan, num_all):
    for month in range(1, 2):
        col_title_list_index = {}
        file_path_cur = fileNamePath + '{}\\'.format(file_year)
        file_name_month = get_excel_file_name(file_path_cur, month)
        logging.debug('文件:{}'.format(file_name_month))
        if os.path.exists(file_name_month):
            sheet_source_month = get_work_sheet(file_name_month)
            for indexCol in range(0, len(col_title_list)):
                col_index = get_col_index(sheet_source_month, col_title_list[indexCol])
                logging.debug('{}:{}'.format(col_title_list[indexCol], col_index))
                col_title_list_index[col_title_list[indexCol]] = col_index
            logging.debug('col_title_list_index：{}'.format(col_title_list_index))
            logging.debug('col_title_list_key：{}'.format(col_title_list_key))
            num_row_max = sheet_source_month.max_row
            num_col_max = sheet_source_month.max_column
            logging.debug('num_row_max：{}'.format(num_row_max))
            logging.debug('num_col_max：{}'.format(num_col_max))
            nCount = 0
            nCountPaiDan = 0
            nCountGongDan = 0
            for rowMonth in range(2, num_row_max+1):
                col_gong_dan_id = col_title_list_index['工单编号']
                col_pai_dan_shi_jian = col_title_list_index['回访时间']
                nCount = nCount + 1
                num_all = num_all + 1
                # logger.debug('{}----{}'.format(col_pai_dan_shi_jian, col_pai_dan_shi_jian))
                if col_pai_dan_shi_jian is None:
                    num_pai_dan = num_pai_dan + 1
                    nCountPaiDan = nCountPaiDan + 1
                    continue
                if col_gong_dan_id is None:
                    num_gong_dan = num_gong_dan + 1
                    nCountGongDan = nCountGongDan + 1
                    continue

                # cell_gong_dan_id = sheet_source_month.cell(rowMonth, col_gong_dan_id).value
                # if cell_gong_dan_id is None:
                #     continue
                # pai_dan_shi_jian_sel = do_execute_sql_select(cursor, cell_gong_dan_id)
                sql_make = ''
                # pai_dan_shi_jian_excel = sheet_source_month.cell(rowMonth, col_pai_dan_shi_jian).value
                # logging.debug('pai_dan_shi_jian_excel：{}'.format(pai_dan_shi_jian_excel))
                # if pai_dan_shi_jian_sel == '':
                sql_make = make_insert_sql(file_year, month, sheet_source_month, rowMonth, col_title_list_index)
                    # logger.debug('{}--{}- sql_make : {}'.format(rowMonth, cell_gong_dan_id, sql_make))
                # logging.debug('insert-{}:'.format(rowMonth))
                # else:
                #     # time_pai_dan_excel_array = time.strptime(pai_dan_shi_jian_excel, "%Y-%m-%d %H:%M:%S")
                #     time_pai_dan_excel_time_stamp = int(time.mktime(pai_dan_shi_jian_excel.timetuple()))
                #     logging.debug('pai_dan_shi_jian_sel : {}'.format(pai_dan_shi_jian_sel))
                #     # time_pai_dan_sql_array = time.strptime(pai_dan_shi_jian_sel, "%Y-%m-%d %H:%M:%S")
                #     time_pai_dan_sql_time_stamp = int(time.mktime(pai_dan_shi_jian_sel.timetuple()))
                #     if time_pai_dan_excel_time_stamp >= time_pai_dan_sql_time_stamp:
                #         sql_make = make_update_sql(sheet_source_month, rowMonth, col_title_list_index, cell_gong_dan_id)
                #         logging.debug('update-{}'.format(rowMonth))
                #     else:
                #         logging.debug('No-{}----{}'.format(time_pai_dan_sql_time_stamp, time_pai_dan_excel_time_stamp))
                #         logging.debug('No-{}----{}'.format(pai_dan_shi_jian_sel, col_pai_dan_shi_jian))
                try:
                    if sql_make != '':
                        logger.debug('{}/{}：{}-{} : {}'.format(rowMonth, num_row_max-1, file_year, month, sql_make))
                        cursor.execute(sql_make)
                        # 提交到数据库执行
                        conn_obj_db.commit()
                except Exception as e:
                    # 发生错误时回滚
                    logger.debug("发生错误时回滚 - sql_make : {}".format(sql_make))
                    # conn_obj_db.rollback()
            # logging.debug('{} 行'.format(nCount))
            logger.debug('工单总数：{}  派单时间为空:{} ---工单编号为空：{}'.format(num_all, nCountPaiDan, nCountGongDan))
        else:
            logger.debug('文件:{}---不存在'.format(file_name_month))


numPaiDanShiJianIsNull = 0
numGongDanIdNull = 0
numAll = 0

# do_import_excel_data(2020, numPaiDanShiJianIsNull, numGongDanIdNull, numAll)
# do_import_excel_data(2021, numPaiDanShiJianIsNull, numGongDanIdNull, numAll)
do_import_excel_data(2023, numPaiDanShiJianIsNull, numGongDanIdNull, numAll)

# 关闭不使用的游标对象
cursor.close()
# 关闭数据库连接
conn_obj_db.close()



